﻿using System;
using System.Web;
using System.Text;
using System.Data;
using System.Linq;
using SubSonic.Schema;
using System.Data.Common;
using SubSonic.Extensions;
using System.Transactions;
using Eacademy.Domain.Models;
using SubSonic.DataProviders;
using Eacademy.Domain.Entities;
using Eacademy.Library.Helpers;
using System.Collections.Generic;
using Eacademy.Domain.Abstractions;
using MsFactory = Microsoft.Practices.EnterpriseLibrary.Data;

namespace Eacademy.Domain.Repository
{
    public class UserRepository : IUserRepository
    {
        EacademyDB db = new EacademyDB();

        public bool UserExist(string username)
        {
            try
            {
                User user = db.Select
                    .From<User>()
                    .Where(UsersTable.UsernameColumn).IsEqualTo(username)
                    .ExecuteSingle<User>();

                if (user != null)
                    return true;
            }
            catch (Exception ex)
            { }
            return false;
        }

        public bool SetLastVisitDate(long userId)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        db.Update<User>()
                            .Set(x => x.UserLastVisit == DateTime.Now)
                            .Where(x => x.UserId == userId)
                            .Execute();
                    }
                    ts.Complete();
                }

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }
        /*
        public bool VerifyUserByConfirmationCode(string confirmationCode, long userId)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        db.Update<User>()
                            .Set(x => x.Verified == true, x => x.ConfirmationCode == String.Empty)
                            .Where(x => x.UserId == userId)
                            .Execute();
                    }
                    ts.Complete();
                }

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;

        }
        
        public User GetUserByConfirmationCode(string confirmationCode)
        {
            User user = new User();
            try
            {
                user = db.Select.From<User>()
                                    .Where(UsersTable.ConfirmationCodeColumn).IsEqualTo(confirmationCode)
                                    .ExecuteSingle<User>();
            }
            catch (Exception)
            {
                return null;
            }
            return user;
        }
        */
        public User GetUserByUsername(string username)
        {
            User user = db.Select
                .From<User>()
                .Where(UsersTable.UsernameColumn).IsEqualTo(username)
                .ExecuteSingle<User>();
            return user;
        }

        public User GetUserByUserId(long userId)
        {
            User user = db.Select
                .From<User>()
                .Where(UsersTable.UserIdColumn).IsEqualTo(userId)
                .ExecuteSingle<User>();
            return user;
        }

        public User GetUserByStudentId(long studentId)
        {
            Student student = db.Select
                .From<Student>()
                .Where(StudentsTable.StudentIdColumn).IsEqualTo(studentId)
                .ExecuteSingle<Student>();

            User user = db.Select
                .From<User>()
                .Where(UsersTable.UserIdColumn).IsEqualTo(student.UserId)
                .ExecuteSingle<User>();

            return user;
        }

        public User GetUserByMentorId(long mentorId)
        {
            Mentor mentor = db.Select
                .From<Mentor>()
                .Where(MentorsTable.MentorIdColumn).IsEqualTo(mentorId)
                .ExecuteSingle<Mentor>();

            User user = db.Select
                .From<User>()
                .Where(UsersTable.UserIdColumn).IsEqualTo(mentor.UserId)
                .ExecuteSingle<User>();

            return user;
        }

        public bool ResetPassword(string password, int userId, string userPasswordSalt)
        {
            try
            {

                using (TransactionScope ts = new TransactionScope())
                {
                    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
                    {
                        String saltedPassword = PasswordHelper.ComputeHash(password, userPasswordSalt);
                        db.Update<User>()
                            .Set(x => x.Password == saltedPassword)
                            .Where(x => x.UserId == userId)
                            .Execute();
                    }
                    ts.Complete();
                }

            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        //public List<UserViewModel> GetUsers()
        //{
        //    int totalCount = 0;
        //    return GetUsers(new UserViewModel(), out totalCount, false);
        //}

        //public List<UserViewModel> GetUsers(UserViewModel parameters)
        //{
        //    int totalCount = 0;
        //    return GetUsers(parameters, out totalCount, false);
        //}

        //public List<UserViewModel> GetUsers(UserViewModel parameters, out int totalCount, bool count = true)
        //{
        //    UserViewModel searchparemeter = parameters;
        //    totalCount = 0;
        //    DbFactory factory = new DbFactory();
        //    MsFactory.Database db = factory.database;
        //    List<UserViewModel> userViewModelList = new List<UserViewModel>();
        //    try
        //    {
        //        StringBuilder sql = new StringBuilder();
        //        sql.AddStartRemove();
        //        sql.AppendLine(@"SELECT u.*, ");
        //        sql.AppendLine(@"       c.country_name, ");
        //        sql.AppendLine(@"       p.city_name, ");
        //        sql.AppendLine(@"       p.firstname, ");
        //        sql.AppendLine(@"       p.lastname, ");
        //        sql.AppendLine(@"       p.company_name ");
        //        sql.AddEndRemove();
        //        sql.AppendLine(@"  FROM users u ");
        //        sql.AddStartRemove();
        //        sql.AppendLine(@"       LEFT JOIN profiles p ");
        //        sql.AppendLine(@"          ON p.user_id = u.user_id ");
        //        sql.AppendLine(@"       LEFT JOIN countries c ");
        //        sql.AppendLine(@"          ON c.country_id = p.country_id ");
        //        sql.AddEndRemove();


        //        DbCommand comm = db.GetSqlStringCommand(sql.ToString());
        //        //db.AddInParameter(comm, "?Keyword", DbType.String, searchparameter.Query);
        //        DataSet ds = factory.ExecuteQuery(comm);

        //        DataTable usersTable = ds.Tables[0];

        //        userViewModelList = usersTable.AsEnumerable().Select(
        //            userRow => new UserViewModel
        //            {
        //                Name = GetName(userRow["user_type_id"].ToInteger(),userRow["firstname"].ToString(), userRow["lastname"].ToString(),userRow["company_name"].ToString()),
        //                Email = userRow["username"].ToString(),
        //                Id = userRow["user_id"].ToInteger(),
        //                TotalEvent = userRow["total_event_count"].ToInteger(),
        //                Location = userRow["city_name"].ToString() + ", "+ userRow["country_name"]
        //            }).ToList<UserViewModel>();

        //        if (count)
        //        {
        //            string countQuery = sql.ToString().GenerateCountQuery();
        //            comm.CommandText = countQuery;
        //            ds = factory.ExecuteQuery(comm);
        //            totalCount = ds.Tables[0].Rows[0]["count"].ToInteger();
        //        }
        //    }
        //    catch (Exception ex)
        //    { }
        //    return userViewModelList;
        //}

    }
}